import pandas as pd
import os

def compare_flight_prices(file_old, file_new):
    # 读取 Excel 数据
    df_old = pd.read_excel(file_old)
    df_new = pd.read_excel(file_new)


    # 将票价列转换为数值型（无法转换的会被置为 NaN）
    df_old["票价"] = pd.to_numeric(df_old["票价"], errors="coerce")
    df_new["票价"] = pd.to_numeric(df_new["票价"], errors="coerce")
    
    # 定义用来判断记录是否相同的关键字段
    key_columns = ["航班号", "出发城市", "到达城市", "航空公司", "出发日期", "出发时间", "到达时间", "中转信息"]
    
    # 按关键字段合并两个数据集
    merged_df = pd.merge(df_old, df_new, on=key_columns, suffixes=("_old", "_new"))
    
    # 删除票价缺失的记录
    merged_df = merged_df[(merged_df["票价_old"].notna()) & (merged_df["票价_new"].notna())]
    merged_df = merged_df[(merged_df["票价_old"] != 0) & (merged_df["票价_new"] != 0)]
    
    # 比较票价
    def price_comparison(row):
        if row["票价_new"] > row["票价_old"]:
            return "高了"
        elif row["票价_new"] < row["票价_old"]:
            return "低了"
        else:
            return "相等"
    
    # 创建票价对比列
    merged_df["票价对比"] = merged_df.apply(price_comparison, axis=1)
    return merged_df

# 确保输出目录存在
output_dir = r"F:\network-collect\compare"
os.makedirs(output_dir, exist_ok=True)
output_file = os.path.join(output_dir, "对比结果.xlsx")

# 定义各 Excel 文件路径
path_old_da_xi = r"F:\network-collect\putout\2025-05-20\大连-西安.xlsx"
path_new_da_xi = r"F:\network-collect\putout\2025-05-21\大连-西安.xlsx"
path_old_xi_da = r"F:\network-collect\putout\2025-05-20\西安-大连.xlsx"
path_new_xi_da = r"F:\network-collect\putout\2025-05-21\西安-大连.xlsx"

# 分别比较两个方向的数据
result_da_xi = compare_flight_prices(path_old_da_xi, path_new_da_xi)
result_xi_da = compare_flight_prices(path_old_xi_da, path_new_xi_da)

# 使用 openpyxl 引擎将两个 DataFrame 写入同一 Excel 文件中不同的工作表
with pd.ExcelWriter(output_file, engine="openpyxl") as writer:
    result_da_xi.to_excel(writer, sheet_name="大连-西安", index=False)
    result_xi_da.to_excel(writer, sheet_name="西安-大连", index=False)

print(f"对比结果已成功存储到 {output_file}")
